IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetAuthenDepartment')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetAuthenDepartment
GO

/****** Object:  StoredProcedure [dbo].[USP_GetAuthenDepartment]    Script Date: 10/27/2010 01:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 24/10/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetAuthenDepartment]
	-- Add the parameters for the stored procedure here
	@staff_code int,
	@department_code int,
	@main_system_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	declare @auth_sub_menu_table as table(auth_department_code int, department_code int, sub_menu_code int)

	-- If you are Super Admin you can add/edit every sub_menu
	if @staff_code = 0
	begin
		insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
		select 0, @department_code, sub_menu_code from stp_sub_menu 
		if @department_code > 0
		begin
			update @auth_sub_menu_table 
				set auth_department_code = auth.auth_department_code, 
					department_code = auth.department_code
			from @auth_sub_menu_table main
			join stp_auth_department auth on main.sub_menu_code = auth.sub_menu_code 
			where auth.department_code = @department_code and auth_department_status = 1
		end
	end
	else
	begin
		-- Staff want to insert sub_menu so display sub_menu from his/her priviledge
		declare @staff_department_code as int
		if @department_code = 0
		begin
			select @staff_department_code = department_code from staff where staff_code = @staff_code 
			insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
			select auth_department_code, department_code, sub_menu_code from stp_auth_department 
			where department_code = @staff_department_code and auth_department_status = 1
		end
		else
		-- Staff want to edit sub_menu so display sub_menu from his/her priviledge
		begin
			select @staff_department_code = department_code from staff where staff_code = @staff_code 
			insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
			select 0, department_code, sub_menu_code from stp_auth_department 
			where department_code = @staff_department_code and auth_department_status = 1

			update @auth_sub_menu_table 
				set auth_department_code = auth.auth_department_code, 
					department_code = auth.department_code
			from @auth_sub_menu_table main
			join stp_auth_department auth on main.sub_menu_code = auth.sub_menu_code 
			where auth.department_code = @department_code and auth_department_status = 1
		end
	end

	/*************************************************/
	/*  Get list of menu from @auth_sub_menu_table   */
	/*                                               */
	/*                                               */
	/*************************************************/
	-- System Menu
		select distinct main.main_system_code,
				main.main_system_name,
				menu.menu_system_code,
				menu.menu_system_name,
				sub_menu.sub_menu_code,
				sub_menu.sub_menu_name,
				sub_menu.sub_menu_url,
				sub_menu.type_code,
				ISNULL(auth.auth_department_code, 0) auth_department_code,
				case ISNULL(auth.auth_department_code, 0) when 0 then 'false' else 'true' end has_authen
		from stp_sub_menu sub_menu
		inner join stp_menu_system menu on menu.menu_system_code = sub_menu.menu_system_code 
		inner join stp_main_system main on main.main_system_code = menu.main_system_code
		inner join @auth_sub_menu_table auth on sub_menu.sub_menu_code = auth.sub_menu_code
		where menu.menu_system_status = 1
				and main.main_system_type > 0
				and @main_system_code in (0, main.main_system_code)
				and menu.menu_system_code in (
						select sub_menu.menu_system_code
						from stp_sub_menu sub_menu
						where sub_menu.sub_menu_status = 1
								--and sub_menu.sub_menu_code in (select sub_menu_code from @auth_sub_menu_table)
						)
		order by main.main_system_code, menu.menu_system_code, sub_menu.type_code

END


GO
